package SpdgBI.PhErp;

import SpdgBI.Common.DBUtils;
import SpdgBI.Common.Udfs;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.Map;

import static com.mchange.v2.c3p0.dbms.OracleUtils.createTemporaryCLOB;

public class Book {
    public void PumpData(String PhName){
        String[] strDataType = {"NUMBER", "NVARCHAR2", "VARCHAR", "NCHAR", "CHAR", "CLOB"};
        List<String> lstDataType = new ArrayList<>(Arrays.asList(strDataType));
        List lstTblInfo = null;
        Connection connBI = null;
        Connection connPUBERP = null;
        PreparedStatement pstmtDp = null;
        PreparedStatement pstmtDataSrc = null;
        ResultSet rsDataSrc = null;
        String strSqlDataSrc = "SELECT a.Bookid BOOKID,\n" +
                "       0 SINDEX,\n" +
                "       BookState BOOKSTATE,\n" +
                "       b.BookTypename BOOKTYPE,\n" +
                "       a.AutoCode AutoCode,\n" +
                "       a.BookName BookName,\n" +
                "       a.ReleaseVersion BookReleaseVersion,\n" +
                "       CAST(a.PrintingVersion AS VARCHAR(10)) PrintingVersion,\n" +
                "       a.ISBN ISBNCode,\n" +
                "       a.CLawType CLawTypeId,\n" +
                "       CLAWTYPE.ClawTypeName ClawTypeName,\n" +
                "       a.CIPType CIPTypeID,\n" +
                "       a.BookPrice BookPrice,\n" +
                "       (CASE\n" +
                "         WHEN a.Unit = 2 THEN\n" +
                "          '册'\n" +
                "         ELSE\n" +
                "          '套'\n" +
                "       END) Unit,\n" +
                "       CAST(a.AverageUnitPrice AS decimal(19, 6)) AverageUnitPrice_6,\n" +
                "       a.BookProperty BookProperty,\n" +
                "       a.BookSrc BookSrc,\n" +
                "       a.FaxReturn FaxReturn,\n" +
                "       a.SalesState SalesState,\n" +
                "       s.SeriesName SeriesInfo,\n" +
                "       a.AuthorName AuthorName,\n" +
                "       a.TranslatorName TranslatorName,\n" +
                "       a.ResponsibleEditor ReportSubmitter_NAME,\n" +
                "       a.DepartmentId ResponsibleEditor_DEPTID,\n" +
                "       RD.DepartmentName ResponsibleEditor_DEPTNAME,\n" +
                "       a.ProjectDepartment ProjectDepartment_DEPTID,\n" +
                "       PD.DepartmentName ProjectDepartment_DEPTname,\n" +
                "       (CASE\n" +
                "         WHEN p.BookFormat > 0 THEN\n" +
                "          p.BookFormat\n" +
                "         ELSE\n" +
                "          a.SpecFormat\n" +
                "       END) BookFormat,\n" +
                "       a.DimensionFormat DimensionFormatID,\n" +
                "       DimensionFormat.DISPLAYNAME DimensionFormatNAME,\n" +
                "       a.SheetCount SheetCount,\n" +
                "       CONVERT(CHAR(10), a.publishdate, 121) PublishDate,\n" +
                "       CONVERT(CHAR(10), a.ReprintDate, 121) PReprintDate,\n" +
                "       a.MAXStorage MAXStorage,\n" +
                "       a.MINStorage MINStorage,\n" +
                "       ISNULL(bcs.CurrentStorage, 0) PubStorageAmountP,\n" +
                "       ISNULL(bcs.CurrentStorage * a.BookPrice, 0) PubStorageListIncome,\n" +
                "       a.SaleDepartmentId SaleDepartmentId_DEPT,\n" +
                "       a.PubPartnerId PubPartnerId_PSN,\n" +
                "       a.GoodsState GoodsState,\n" +
                "       a.FinishedProductSize FinishedProductSize,\n" +
                "       a.OperationKind OperationKind,\n" +
                "       a.ReimpressionKind ReimpressionKind,\n" +
                "       a.KindCode KindCode,\n" +
                "       a.KnowledgeKind KnowledgeKind,\n" +
                "       a.GradeCode GradeCode,\n" +
                "       a.IsPublicBidding IsPublicBidding,\n" +
                "       a.OtherKind OtherKind,\n" +
                "       a.BooksNumber AmountPerPackage,\n" +
                "       a.PlaceCode PlaceCode,\n" +
                "       a.ISRC ISRC,\n" +
                "       a.AuthType AuthType,\n" +
                "       a.HasNetworkAuth HasNetworkAuth,\n" +
                "       a.HasPublished HasPublished,\n" +
                "       a.HasContract HasContract_dis,\n" +
                "       p.InteriorClassNo InteriorClassNo,\n" +
                "       p.PublishMode PublishMode,\n" +
                "       a.CooperativePublishId CooperativePublishId,\n" +
                "       a.Content,\n" +
                "       a.Foreword,\n" +
                "       a.AuthorIntroduce,\n" +
                "       a.ExpertsComment,\n" +
                "       a.FinanceType FinanceTypeID,\n" +
                "       FT.FinanceTypeName FinanceTypeNAME\n" +
                "  FROM Book a\n" +
                "  LEFT OUTER JOIN Series s\n" +
                "    ON a.SeriesInfoId = s.SeriesId\n" +
                "  LEFT JOIN BookType b\n" +
                "    ON a.BookType = b.BookTypeId\n" +
                "  LEFT JOIN Project p\n" +
                "    ON p.ProjectId = a.ProjectId\n" +
                "  LEFT JOIN BookCurrentStorage bcs\n" +
                "    ON bcs.BookId = a.BookId\n" +
                "  LEFT JOIN CLAWTYPE\n" +
                "    ON CLAWTYPE.clawtypeid = a.CLawType\n" +
                "  LEFT JOIN DimensionFormat\n" +
                "    ON DimensionFormat.DimensionFormatId = a.DimensionFormat\n" +
                "  LEFT JOIN Department RD\n" +
                "    ON RD.DepartmentId = a.DepartmentId\n" +
                "  LEFT JOIN Department PD\n" +
                "    ON PD.DepartmentId = a.DepartmentId\n" +
                "  LEFT JOIN FinanceType FT\n" +
                "    ON FT.FinanceTypeId = a.FinanceType\n";
        String strDel = "DELETE FROM BASE_PUB_BOOK WHERE UNITCODE = ?";
        Long endTime;
        Long StartTime;
        try {
            StartTime = System.currentTimeMillis();
            connPUBERP = DBUtils.getConnection(PhName);
            pstmtDataSrc = connPUBERP.prepareStatement(strSqlDataSrc);
            rsDataSrc = pstmtDataSrc.executeQuery();

            connBI = DBUtils.getConnection("SPDGBI");
            connBI.setAutoCommit(false);
            lstTblInfo = Udfs.GetTableInfo("BASE_PUB_BOOK");

            pstmtDp = connBI.prepareStatement(strDel);
            pstmtDp.setString(1,PhName);
            pstmtDp.execute();

            pstmtDp = connBI.prepareStatement(((Map)lstTblInfo.get(lstTblInfo.size() - 1)).get("sqlins").toString());

            oracle.sql.CLOB clob = null;
            while (rsDataSrc.next()) {
                pstmtDp.setString(1, PhName);
                for (int i = 1; i < lstTblInfo.size() - 1; i++) {
                    switch (lstDataType.indexOf(((Map)lstTblInfo.get(i)).get("DATA_TYPE").toString())) {
                        case 0:
                            pstmtDp.setFloat(i + 1, rsDataSrc.getFloat(((Map)lstTblInfo.get(i)).get("COLUMN_NAME").toString()));
                            break;
                        case 1:
                        case 2:
                        case 3:
                        case 4:
                            pstmtDp.setString(i + 1, rsDataSrc.getString(((Map)lstTblInfo.get(i)).get("COLUMN_NAME").toString()));
                            break;
                        case 5:
                            clob = createTemporaryCLOB(connBI, false, oracle.sql.CLOB.DURATION_SESSION);
                            clob.open(oracle.sql.CLOB.MODE_READWRITE);
                            clob.setString(1, rsDataSrc.getString(lstTblInfo.get(i).toString()));
                            pstmtDp.setClob(i + 1, clob);
                            break;
                        default:
                            pstmtDp.setObject(i + 1, rsDataSrc.getString(((Map)lstTblInfo.get(i)).get("COLUMN_NAME").toString()));
                            break;
                    }
                }
                pstmtDp.addBatch();
            }
            pstmtDp.executeBatch();
            pstmtDp.clearParameters();
            pstmtDp.clearBatch();
            connBI.commit();
            endTime = System.currentTimeMillis();
            System.out.println("抽取数据完成，出版单位编码为" + PhName + "的书卡数据抽取完成，用时：" + (endTime - StartTime));

        } catch (Exception ex) {
            ex.printStackTrace();
        }finally {
            lstTblInfo = null;
            DBUtils.close(connBI);
        }

    }

    public void PumpDataAll(){
        String[] PhList = {"010200", "0104", "010500", "010600", "010700", "0109","011000"};

        for (int i = 0; i < PhList.length; i++) {
            try {
                PumpData(PhList[i]);
            }catch (Exception ex){
                ex.printStackTrace();
                System.out.println("抽取数据时出错，抽取出版单位编码为" + PhList[i] + "的书卡数据没有成功");
            }
        }

    }
}
